using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Npgsql;

namespace pgImport
{
    class DatabaseInfo
    {
        private NpgsqlConnection conn = null;
        private List<TableInfo> tables = new List<TableInfo>();

        /// <summary>
        /// Class constructor
        /// </summary>
        /// <param name="connection_string">Valid conn string</param>
        public DatabaseInfo(String connection_string)
        {
            // Attempt to connect to database & gather info ...
            try
            {
                conn = new NpgsqlConnection(connection_string);
                conn.Open();
                conn.Close();

                // Retrieve info on table_list & fields 
                GetTableList();
            }
            catch (Exception ex)
            {
                conn = null;
                Console.WriteLine(ex.Message);
            }
        }

        #region Public Properties

        public Boolean GotDatabaseInformation
        {
            get { return (conn == null ? true : false); }
        }

        public List<TableInfo> Tables        
        {
            get { return tables; }
        }

        #endregion

        #region Private Members

        /// <summary>
        /// Retrieves a list of table_list for selected database
        /// </summary>
        private void GetTableList()
        {
            NpgsqlCommand command;
            NpgsqlDataReader dr;

            conn.Open();
            ArrayList table_list = new ArrayList();

            try
            {
                command = new NpgsqlCommand("select tablename from pg_tables where schemaname LIKE 'public' order by tablename ASC", conn);
                dr = command.ExecuteReader();

                // For each table ...
                while (dr.Read())
                {
                    // ... Append it to the table list 
                    table_list.Add(dr[0]);
                }

                // For each table, again ...
                foreach (String table in table_list)
                {
                    StringBuilder tmp = new StringBuilder();
                    Hashtable field_list = new Hashtable();
                    TableInfo new_table = new TableInfo(table);

                    tmp.Append("select column_name, data_type from information_schema.columns where table_schema = 'public' and table_name = '");
                    tmp.Append(table);
                    tmp.Append("' order by ordinal_position;");

                    command = new NpgsqlCommand(tmp.ToString(), conn);
                    dr = command.ExecuteReader();

                    // For each field ...
                    while (dr.Read())
                    {
                        field_list.Add(dr[0], dr[1]);
                    }

                    // Add fields to new table object
                    new_table.Fields = field_list;
                    tables.Add(new_table);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                // Ensure conn is ALWAYS closed
                conn.Close();
            }
        }

        /// <summary>
        /// Retrieves a list of fields for selected database
        /// </summary>
        private void GetFieldList()
        {
        }

        #endregion
    }
}
